﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.DBM;
using KPIS.GERP.GBMS.DAL;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DALFactory;
using KPIS.GERP.GBMS.IBLL;
using System.Globalization;

namespace KPIS.GERP.GHRMS.BLL
{
    public sealed class EmployeeBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public DataTable ListEmployeeNew()
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@DeleteStatus", SystemInfo._DeleteRecordStatus);
                string sql =
@"SELECT 
	j.job_site_seq, 
	j.job_site_name, 
	j.head_pos_seq,
	p.pos_name,
	e.pos_seq,
	e.emp_seq,
	e.first_name,
	e.last_name,
    j.job_site_name as JobsiteName,
concat(e.first_name, '  ', e.last_name, ' (', p.pos_name , ')' ) as fullnamepos
concat(e.first_name, '  ', e.last_name, ' (', p.pos_name , ')' ) as fullnamepos

FROM hrm_ps_employees e 
    LEFT JOIN hrm_ps_employee_statuses es ON e.emp_status_seq = es.emp_status_seq
    LEFT JOIN hrm_mp_positions p ON e.pos_seq = p.pos_seq
    LEFT JOIN hrm_mp_job_sites j ON e.working_job_site_seq = j.job_site_seq

WHERE e.record_status not like @DeleteStatus
and es.active = 1
";

                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {

            }
            return null;
        }

        public string GetWorkingJobSiteByEmpSeq(string emp_seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                string sql;
                sql = " select working_job_site_seq"
                    + " from hrm_ps_employees"
                    + " where emp_seq = @emp_seq";

                DataTable dt;
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();
                string workingJobSiteSeq = dt.Rows[0]["working_job_site_seq"].ToString();
                string jobSiteSeq = CheckJobSiteLevel(workingJobSiteSeq);
                return jobSiteSeq;
            }
            catch (Exception ex)
            {

            }
            return null;
        }

        public string CheckJobSiteLevel(string job_site_seq)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@job_site_seq", job_site_seq);
                string sql;

                sql = " select job_site_level_seq, upper_job_site_seq"
                    + " from hrm_mp_job_sites"
                    + " where job_site_seq = @job_site_seq"
                    + " and job_site_level_seq >= 3";

                dbManager.Open();
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                dbManager.Dispose();

                if (dtb.Rows.Count > 0)
                {
                    string job_site_level_seq = dtb.Rows[0]["job_site_level_seq"].ToString();

                    if (job_site_level_seq == "3")
                    {
                        return job_site_seq;
                    }
                    else
                    {
                        return CheckJobSiteLevel(dtb.Rows[0]["upper_job_site_seq"].ToString());
                    }
                } else
                {
                    return job_site_seq;
                }
            }
            catch (Exception ex)
            {

            }
            //finally
            //{
            //    dbManager.Dispose();
            //}

            return null;
        }

        public DataTable ListEmployeeBySeq(string Sequence)
        {
            DataTable dt; 
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
            string sql;
            sql = " select e.emp_seq as sequence, e.pos_seq as t_pos_seq, e.class_seq as t_class_seq"
                + ", e.working_job_site_seq, mp.exec_pos_seq"
                + " ,CONCAT( coalesce(np.prefix_name, ''), ' ', e.first_name,' ',e.last_name ) full_name"
                    
                + ", mp.pos_name, ex.exec_pos_name, c.class_name, j.job_site_name"
                + ", '' as misc"
                + ", mp.pos_name as posName"
                + ", c.class_name as className"
                + ", j.job_site_name as jobsiteName"

                + " from hrm_ps_employees e"
                + " left join hrm_mp_positions mp on e.pos_seq = mp.pos_seq"
                + " left join hrm_mp_exec_positions ex on mp.exec_pos_seq = ex.exec_pos_seq"
                + " left join hrm_mp_classes c on e.class_seq = c.class_seq"
                + " left join hrm_mp_job_sites j on e.working_job_site_seq = j.job_site_seq"
                ////+ ", hrm_mp_exec_positions ex"
                //+ ", hrm_mp_job_sites as js"
                //+ ", hrm_mp_classes as mc"
                + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"

                //+ " where e.pos_seq = mp.pos_seq"
                ////+ " and p.exec_pos_seq = ex.exec_pos_seq"
                //+ " and e.working_job_site_seq = js.job_site_seq"
                //+ " and e.class_seq = mc.class_seq"
                //+ " and e.name_prefix_seq = np.name_prefix_seq"
                + (!string.IsNullOrEmpty(Sequence) ? " where e.emp_seq = " + Sequence : "");

            DataSet ds;
            ds = new DataSet();
            dbManager.Open();
            ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
            dt = ds.Tables[0];
            dbManager.Dispose(); 
            return dt;
        }

        public DataTable ListEmployeeByType(string Type, string JobSite)
        {
            try
            {
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                string sql;
                sql = " select e.emp_seq as sequence" 
                    + ", e.pos_seq as t_pos_seq" +
                      ", e.class_seq as t_class_seq" +
                      ", mp.pos_ref as ref_no" +
                      ", mp.pos_name as pos_name" +
                      ", mc.class_name as class_name" +
                      ", js.job_site_name as job_site_name"
                    + " ,CONCAT( np.prefix_name, ' ', e.first_name,' ',e.last_name ) full_name"
                    + ", exe.exec_pos_seq, exe.exec_pos_name "
                    + ", '' as misc "
                    + " from hrm_ps_employees e"
                    + " left join hrm_mp_positions mp on e.pos_seq = mp.pos_seq"
                    + " left join hrm_mp_exec_positions exe on mp.exec_pos_seq = exe.exec_pos_seq"
                    + " left join hrm_mp_job_sites js on e.working_job_site_seq = js.job_site_seq"
                    + " left join hrm_mp_classes mc on e.class_seq = mc.class_seq"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                    + " where e.record_status != 'D' ";
                    if (Type != "")
                    {
                        sql += " and e.emp_type_seq = " + Type;
                    }
                    if (JobSite != "")
                    {
                        sql += " and e.working_job_site_seq = " + JobSite;
                    }
                    sql += " order by full_name";

                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {

            }
            return null;
        }

        public DataTable ListEmployeePosByType(string Type)
        {
            try
            {
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@EmpTypeSeq", Type);
                string sql; 
                //sql = " select CONCAT(cast(e.emp_seq as char),'-', cast(e.pos_seq as char)) sequence"
                //    + " ,CONCAT( np.prefix_name, ' ', e.first_name,' ',e.last_name ) full_name"
                //    + " from hrm_ps_employees as e"
                //    //+ ", hrm_mp_positions as mp"
                //    //+ ", hrm_mp_job_sites as js"
                //    //+ ", hrm_mp_classes as mc"
                //    + ", mas_name_prefixes as np"

                //    + " where 0=0"
                
                //    //+ " and e.pos_seq = mp.pos_seq"
                //    //+ " and e.working_job_site_seq = js.job_site_seq"
                //    //+ " and e.class_seq = mc.class_seq"
                //    + " and e.name_prefix_seq = np.name_prefix_seq"
                //    + " and e.emp_type_seq = " + Type
                //    + " order by full_name";
                sql = @" 
select CONCAT(cast(e.emp_seq as char),'-', cast(e.pos_seq as char)) as sequence,
    CONCAT( np.prefix_name, ' ', e.first_name,' ',e.last_name ) as full_name

from hrm_ps_employees as e 
    left join  mas_name_prefixes as np on e.name_prefix_seq = np.name_prefix_seq and np.record_status = @RecordStatus
    left join hrm_ps_employee_statuses as es on es.emp_status_seq = e.emp_status_seq and es.record_status = @RecordStatus and es.Active = 1 
			

				 where e.emp_type_seq = @EmpTypeSeq and e.record_status = @RecordStatus 

				 order by full_name";

                DataSet ds;
                DataTable dt;
                ds = new DataSet();
                dbManager.Open();
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0];
                dbManager.Dispose();
                return dt;
            }
            catch (Exception ex)
            {

            }
            return null;
        }

        //public DataTable ListEmployeePosition(string pos_seq, string exec_pos_seq)
        //{
        //    try
        //    {
        //        dbManager.CreateParameters(1);
        //        dbManager.AddParameters(0, "@RecordStatus", SystemInfo._ActiveRecordStatus);
        //        string sql;
        //        sql = " select CONCAT(cast(e.emp_seq as char),'-', cast(e.pos_seq as char)) sequence"
        //            + " ,CONCAT( np.prefix_name, e.first_name,' ',e.last_name ) full_name"
        //            + " from hrm_ps_employees as e"
        //            + ", hrm_mp_positions as mp"
        //            + ", hrm_mp_job_sites as js"
        //            + ", hrm_mp_classes as mc"
        //            + ", mas_name_prefixes as np"

        //            + " where e.pos_seq = mp.pos_seq"
        //            + " and e.working_job_site_seq = js.job_site_seq"
        //            + " and e.class_seq = mc.class_seq"
        //            + " and e.name_prefix_seq = np.name_prefix_seq";
        //        if (pos_seq.Length > 0)
        //            sql += " and mp.pos_seq = " + pos_seq;
        //        if (exec_pos_seq.Length > 0)
        //            sql += " and exec_pos_seq = " + exec_pos_seq;
        //        sql += " order by full_name";

        //        DataSet ds;
        //        DataTable dt;
        //        ds = new DataSet();
        //        dbManager.Open();
        //        ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
        //        dt = ds.Tables[0];
        //        dbManager.Dispose();
        //        return dt;
        //    }
        //    catch (Exception ex)
        //    {

        //    }
        //    return null;
        //}
    }
}
